<?php
class Tools_Excel
{
    private $resultPHPExcel;
    private $_rows_index ;

    public function __construct ()
    {
        $this->resultPHPExcel = new PHPExcel();
        $this->_rows_index = 1;
    }

    private static function build_cols_key($ti)
    {
        if ($ti < 26)
        {
            return chr(65 + $ti);
        }
        else
        {
            return chr(65 + intval($ti/26) - 1). chr(65 + $ti%26);
        }
    }

    public function save ($filename)
    {
        $xlsWriter = new PHPExcel_Writer_Excel5($this->resultPHPExcel);
        $xlsWriter->save($filename);

        if (file_exists($filename))
        {
            chmod($filename, 0666);
        }
    }

    public function h1 ($str, $width = 1, $color='FFFF0000')
    {
        $key1 = self::build_cols_key(0) . $this->_rows_index;
        $key2 = self::build_cols_key($width) . $this->_rows_index;
        $font_color = new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_WHITE);

        $this->resultPHPExcel->getActiveSheet()->getStyle($key1)->getFont()->setColor($font_color);
        $this->resultPHPExcel->getActiveSheet()->setCellValue($key1, $str);
        $this->resultPHPExcel->getActiveSheet()->mergeCells("$key1:$key2");

        $this->resultPHPExcel->getActiveSheet()->getStyle($key1)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $this->resultPHPExcel->getActiveSheet()->getStyle($key1)->getFill()->getStartColor()->setARGB($color);
        $this->resultPHPExcel->getActiveSheet()->getStyle($key1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $this->_rows_index++;
    }


    public function add_sheet($title, $index = 0)
    {
        if ($index > 0)
        {
            $sheet = new PHPExcel_Worksheet($this->resultPHPExcel, $title); 
            $this->resultPHPExcel->addSheet($sheet);
            $this->resultPHPExcel->setActiveSheetIndex($index);
            $this->_rows_index = 1;
        }
        else
        {
            $this->resultPHPExcel->getActiveSheet()->setTitle($title);
        }
    }

    public function build_breakline ()
    {
        $this->_rows_index++;
    }

    public function build_data ($data = array(), $title, $footer, $hd_color, $ft_color)
    {

        // header
        $ti = 0;
        $tkeys = array();
        $color = new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_WHITE);
        foreach ($title  as $val)
        {
            $key  = self::build_cols_key($ti);
            $tkey = $key. $this->_rows_index;
            $tkeys[$ti] = $key;
            $this->resultPHPExcel->getActiveSheet()->getStyle($tkey)->getFont()->setColor($color);
            $this->resultPHPExcel->getActiveSheet()->getStyle($tkey)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $this->resultPHPExcel->getActiveSheet()->getStyle($tkey)->getFill()->getStartColor()->setARGB($hd_color);
            $this->resultPHPExcel->getActiveSheet()->setCellValue($tkey, $val);

            ++$ti;
        }
        $this->_rows_index++;

        // data
        foreach($data as $item) 
        {
            for ($ii = 0; $ii < count($item); ++$ii)
            {
                $this->resultPHPExcel->getActiveSheet()->setCellValue($tkeys[$ii] . $this->_rows_index, $item[$ii]);
            }

            $this->_rows_index++;
        }

        // footer
        $ti = 0;
        $tkeys = array();
        foreach ($footer  as $val)
        {
            $key  = self::build_cols_key($ti);
            $tkey = $key. $this->_rows_index;
            $tkeys[$ti] = $key;
            $this->resultPHPExcel->getActiveSheet()->setCellValue($tkey, $val);
            $this->resultPHPExcel->getActiveSheet()->getStyle($tkey)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $this->resultPHPExcel->getActiveSheet()->getStyle($tkey)->getFill()->getStartColor()->setARGB($ft_color);

            ++$ti;
        }
        $this->_rows_index++;
    }


    public static function export2($data = array(), $title = array(), $filename='report')
    {
        // 新建
        $resultPHPExcel = new PHPExcel();

        // 设值
        $ti = 0;
        $tkeys = array();
        foreach ($title  as $val)
        {
            $key  = self::build_cols_key($ti);
            $tkey = $key.'1';
            $tkeys[$ti] = $key;
            $resultPHPExcel->getActiveSheet()->setCellValue($tkey, $val);

            ++$ti;
        }

        $i = 2;
        foreach($data as $item) 
        {
            for ($ii = 0; $ii < count($item); ++$ii)
            {
                $resultPHPExcel->getActiveSheet()->setCellValue($tkeys[$ii] . $i, $item[$ii]);
            }

            $i++;
        }

        // 设置导出文件名
        $outputFileName = $filename.'.xls';
        $xlsWriter = new PHPExcel_Writer_Excel5($resultPHPExcel);

        //ob_start(); ob_flush();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/vnd.ms-excel");
        header("Content-Type: application/download");
        header('Content-Disposition:attachment;filename="'.$outputFileName.'"');
        header("Content-Transfer-Encoding: binary");
        header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");

        /*
        header("Content-type:application/octet-stream");
        header("Accept-Ranges:bytes");
        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:attachment;filename=".$filename.".xls");
        header("Pragma: no-cache");
        header("Expires: 0");
        */
 
        $xlsWriter->save( "php://output" );
        exit;
    }


    /**
     * 导出数据为excel表格
     * @param $data    一个二维数组,结构如同从数据库查出来的数组
     * @param $title   excel的第一行标题,一个数组,如果为空则没有标题
     * @param $filename 下载的文件名
     * @examlpe
        $stu = M ('User');
        $arr = $stu -> select();
        exportexcel($arr,array('id','账户','密码','昵称'),'文件名!');
    */
    public static function export($data=array(),$title=array(),$filename='report') {
        header("Content-type:application/octet-stream");
        header("Accept-Ranges:bytes");
        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:attachment;filename=".$filename.".xls");
        header("Pragma: no-cache");
        header("Expires: 0");
        //导出xls 开始
        if (!empty($title)) {
            foreach ($title as $k => $v) {
                $title[$k]=iconv("UTF-8", "GB2312",$v);
            }
            $title= implode("\t", $title);
            echo "$title\n";
        }
        if (!empty($data)) {
            foreach($data as $key=>$val) {
                foreach ($val as $ck => $cv) {
                    $data[$key][$ck]=iconv("UTF-8", "GB2312", $cv);
                }
                $data[$key]=implode("\t", $data[$key]);

            }
            echo implode("\n",$data);
        }
    }

}
